package rsj.service.report.dynamic;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import rsj.api.report.entity.ReportParam;

/**
 * 接口报表动态sql
 *
 * @author yuanqinglong
 * @since 2021/7/27 9:32
 */
@Component
public class ProductReportDynamicStatement {


    //**********************************接口报表*************************************//


    /**
     * 接口报告列出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productReportListSql(ReportParam reportParam) {
        return this.productReportExportSql(reportParam) + " OFFSET #{pageOffset} LIMIT #{pageSize}";
    }


    /**
     * 接口报告导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productReportExportSql(ReportParam reportParam) {
        String sql = "SELECT\n" +
                "stat_prod_uid AS prod_uid,\n" +
                "MAX(stat_prod_name) AS prod_name,\n" +
                "sp.prty_fname,\n" +
                "prod_type_fid,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "LEFT JOIN sys.sys_prty sp ON sp.prty_fid = p.prod_type_fid" +
                " WHERE stat_date BETWEEN #{startDate} AND #{endDate}";
        sql += this.productReportQueryConditions(reportParam);
        sql += " GROUP BY prty_fname,stat_prod_uid ,prod_type_fid ORDER BY stat_prod_uid";
        return sql;
    }


    /**
     * 接口报告统计sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productReportCountSql(ReportParam reportParam) {
        String sql = "SELECT COUNT(* ) FROM (\n" +
                "  SELECT stat_prod_uid\n" +
                "  FROM sys.sys_stat sta\n" +
                "  LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "  LEFT JOIN sys.sys_prty sp ON sp.prty_fid = p.prod_type_fid WHERE stat_date BETWEEN #{startDate} AND #{endDate}";
        sql += this.productReportQueryConditions(reportParam) + " GROUP BY prty_fname, stat_prod_uid, prod_type_fid ) a";
        return sql;
    }



    /**
     * 接口报表查询条件
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productReportQueryConditions(ReportParam reportParam) {
        String sql = "";
        if (StringUtils.isNotBlank(reportParam.getProdUid())) {
            sql += " AND prod_uid LIKE #{%prodUid%}";
        }
        if (StringUtils.isNotBlank(reportParam.getProdTypeFid())) {
            sql += " AND prod_type_fid = #{prodTypeFid}";
        }
        return sql;
    }






    /**
     * 接口报告详情sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productReportDetailListSql(ReportParam reportParam) {
        return this.productReportDetailExportSql(reportParam) + " OFFSET #{pageOffset} LIMIT #{pageSize}";
    }


    /**
     * 接口报告详情导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productReportDetailExportSql(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        String sql = "SELECT\n" +
                "TO_CHAR(stat_date, '" + grouping + "') AS stat_date,stat_prod_uid AS prod_uid,\n" +
                "MAX(prty_fname) AS prty_fname,\n" +
                "MAX(stat_prod_name) AS prod_name ,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "LEFT JOIN sys.sys_prty pt ON p.prod_type_fid = pt.prty_fid" +
                " WHERE stat_date BETWEEN #{startDate} AND #{endDate} AND prod_uid = #{prodUid}";
        if (StringUtils.isNotBlank(reportParam.getProdTypeFid())) {
            sql += " AND prod_type_fid = #{prodTypeFid}";
        }
        sql += " GROUP BY stat_prod_uid ,TO_CHAR(stat_date, '" + grouping + "')  ORDER BY stat_prod_uid";
        return sql;
    }



    /**
     * 接口报告详情导出统计
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productReportDetailExportCount(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        String sql = "SELECT COUNT(*) FROM (\n" +
                "SELECT\n" +
                "stat_prod_uid\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "LEFT JOIN sys.sys_prty pt ON p.prod_type_fid = pt.prty_fid" +
                " WHERE stat_date BETWEEN #{startDate} AND #{endDate} AND prod_uid = #{prodUid}";
        if (StringUtils.isNotBlank(reportParam.getProdTypeFid())) {
            sql += " AND prod_type_fid = #{prodTypeFid}";
        }
        sql += " GROUP BY stat_prod_uid ,TO_CHAR(stat_date, '" + grouping + "') ORDER BY stat_prod_uid ) a";
        return sql;
    }
}
